﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace Utils.Npoi
{
    public class NpoiExcelHelper
    {
        private IWorkbook workbook;
        private ISheet sheet;
        private readonly IFormulaEvaluator formula;
        private readonly string fullPath;

        public List<string> SheetNames;

        public NpoiExcelHelper()
        {
            workbook = new XSSFWorkbook();
            sheet = workbook.CreateSheet();
            sheet.ForceFormulaRecalculation = true;
            SheetNames = new List<string>();
            GetSheetNames();
        }

        public NpoiExcelHelper(string fileName)
        {
            if (!File.Exists(fileName)) throw new IOException("文件:" + fileName + "不存在");
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite))
            {
                bool isx = Path.GetExtension(fileName).Equals(".xlsx");
                workbook = isx ? new XSSFWorkbook(fs) : new HSSFWorkbook(fs) as IWorkbook;
                formula = isx ? new XSSFFormulaEvaluator(workbook) : new HSSFFormulaEvaluator(workbook) as IFormulaEvaluator;
            }
            sheet = workbook.GetSheetAt(0);
            sheet.ForceFormulaRecalculation = true;

            SheetNames = new List<string>();
            GetSheetNames();
            fullPath = fileName;
        }

        public List<string> GetSheetNames()
        {
            SheetNames.Clear();
            for (int i = 0; i < workbook.NumberOfSheets; ++i)
            {
                if (workbook.IsSheetHidden(i)) continue;
                SheetNames.Add(workbook.GetSheetAt(i).SheetName);
            }
            return SheetNames;
        }

        public void SetSheet(string sheetName)
        {
            if (!SheetNames.Any(sn => sn.Equals(sheetName))) throw new Exception("不存在的名为" + sheetName + "的工作簿");

            sheet = workbook.GetSheet(sheetName);
            sheet.ForceFormulaRecalculation = true;
        }

        public void CreateSheet(string sheetName)
        {
            workbook.CreateSheet(sheetName);
            SheetNames.Add(sheetName);
        }

        public void SetSheetName(int sheetIdx, string sheetName)
            => workbook.SetSheetName(sheetIdx, sheetName);

        public string Read(string cellName)
            => GetStringValue(GetCell(cellName));

        public DataTable ReadToDataTable(bool isHeader, string firstCell, string lastCell)
            => ReadToDataTable(isHeader, GetRowIndex(firstCell), GetColumnIndex(firstCell), GetRowIndex(lastCell), GetColumnIndex(lastCell));

        public DataTable ReadToDataTable(bool isHeader, string firstCell)
        {
            int firstRowIndex = GetRowIndex(firstCell);
            return ReadToDataTable(isHeader, firstRowIndex, GetColumnIndex(firstCell), sheet.LastRowNum, sheet.GetRow(firstRowIndex).Cells.Count - 1);
        }

        public List<string> ReadToList(string firstCell)
            => ReadToList(GetRowIndex(firstCell), sheet.LastRowNum, GetColumnIndex(firstCell));

        public List<string> ReadToList(int firstRow, int column)
            => ReadToList(firstRow, sheet.LastRowNum, column);

        public List<string> ReadToList(int firstRow, int lastRow, int column)
        {
            List<string> list = new List<string>();
            IRow row;
            for (int i = firstRow; i <= lastRow; ++i)
            {
                row = sheet.GetRow(i);
                list.Add(GetStringValue(row.GetCell(column)));
            }
            return list;
        }

        public DataTable ReadToDataTable(bool isHeader, int firstRow, int firstColumn, int lastRow, int lastColumn)
        {
            IRow row = sheet.GetRow(firstRow);

            DataTable dt = new DataTable(sheet.SheetName);

            for (int i = firstColumn; i <= lastColumn; ++i)
            {
                dt.Columns.Add(new DataColumn(isHeader ? GetNewColumnName(dt.Columns, GetStringValue(row.Cells[i])) : GetColumnName(i)));
            }

            for (int i = isHeader ? ++firstRow : firstRow; i <= lastRow; ++i)
            {
                DataRow dr = dt.NewRow();
                row = sheet.GetRow(i);
                for (int j = firstColumn, cIdx = 0; j <= lastColumn; ++j, ++cIdx)
                {
                    if (j >= row.Cells.Count) break;
                    dr[cIdx] = GetStringValue(row.GetCell(j));
                }

                dt.Rows.Add(dr.ItemArray);
            }

            return dt.Copy();
        }

        public void Write(int rowIdx, int colIdx, string val)
        {
            Write(GetCell(rowIdx, colIdx), val);
            Formula();
        }

        public void Write(string cellName, string val) => Write(GetCell(cellName), val);

        public void Write(int rowIdx, int colIdx, DataTable dt, bool isHeader)
        {
            if (!HasData(dt)) return;

            IRow row;
            ICell cell;
            int cIdx;

            if (isHeader)
            {
                row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);
                ++rowIdx;
                cIdx = colIdx;
                foreach (DataColumn col in dt.Columns)
                {
                    cell = row.GetCell(cIdx) ?? row.CreateCell(cIdx);
                    ++cIdx;
                    cell.SetCellValue(col.ColumnName);
                }
                SetRowFontBold(row);
            }

            foreach (DataRow dataRow in dt.Rows)
            {
                row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);
                ++rowIdx;
                cIdx = colIdx;
                foreach (object data in dataRow.ItemArray)
                {
                    cell = row.GetCell(cIdx) ?? row.CreateCell(cIdx);
                    ++cIdx;

                    cell.SetCellValue(data?.ToString());
                }
            }
        }

        public void Write(string cellName, DataTable dt, bool isHeader)
            => Write(GetRowIndex(cellName), GetColumnIndex(cellName), dt, isHeader);

        public void Write(string cellName, List<string> list)
            => Write(GetRowIndex(cellName), GetColumnIndex(cellName), list);

        public void Write(int rowIdx, int colIdx, List<string> list)
        {
            IRow row;
            ICell cell;

            list.ForEach(a =>
            {
                row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);
                ++rowIdx;

                cell = row.GetCell(colIdx) ?? row.CreateCell(colIdx);
                cell.SetCellValue(a);
            });

        }

        public void Merged(string firstCell, string lastCell)
            => Merged(GetRowIndex(firstCell), GetRowIndex(lastCell), GetColumnIndex(firstCell), GetColumnIndex(lastCell));

        public void Merged(int firstRow, int lastRow, int firstCol, int lastCol)
            => sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

        public string SaveToExcel(string path, bool overwrite)
        {
            if (string.IsNullOrWhiteSpace(path))
            {
                path = Path.GetTempPath() + Path.GetFileNameWithoutExtension(Path.GetTempFileName()) + ".xlsx";
                overwrite = false;
            }

            string oldFileName = Path.GetFileNameWithoutExtension(path),
                   suffix = Path.GetExtension(path),
                   newDirectory = Path.GetDirectoryName(path),
                   newFileName = Path.GetFileNameWithoutExtension(Path.GetTempFileName()),
                   newPath = newDirectory + "\\" + newFileName + suffix;
            try
            {
                if (!Directory.Exists(newDirectory))
                {
                    Directory.CreateDirectory(newDirectory);
                }
                using (FileStream fs = new FileStream(newPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    workbook.Write(fs);
                }
            }
            catch (IOException)
            {
                throw new IOException("保存失败");
            }

            if (!overwrite) return newPath;
            try
            {
                RenameFile(newPath, path);
            }
            catch (IOException)
            {
                // ignored
            }
            return path;
        }

        public string SaveToExcel()
            => SaveToExcel(true);

        public string SaveToExcel(bool overwrite)
            => SaveToExcel(fullPath, overwrite);

        public void Formula()
            => formula.EvaluateAll();

        public void SetRowFontBold(string firstCell, string lastCell) => SetRowFontBold(GetRowIndex(firstCell), GetRowIndex(lastCell), GetColumnIndex(firstCell), GetColumnIndex(lastCell));

        public void SetRowFontBold(int firstRow, int lastRow, int firstCol, int lastCol)
        {
            for (; firstRow >= lastRow; firstRow++)
            {
                SetRowFontBold(sheet.GetRow(firstRow));
            }
        }

        private void SetRowFontBold(IRow row)
        {
            if (row == null || !row.Cells.Any()) return;

            ICellStyle style;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.Boldweight = 700;

            row.Cells.ForEach(cell =>
            {
                style = cell.CellStyle ?? workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.SetFont(font);

                cell.CellStyle = style;
            });
        }

        public void SetSheetName(string sheetName)
            => workbook.SetSheetName(SheetNames.IndexOf(sheet.SheetName), sheetName);

        public void ProtectSheet(string password) => sheet.ProtectSheet(password);

        private static void Write(ICell cell, string val)
            => cell.SetCellValue(val);

        private ICell GetCell(int rowIdx, int colIdx)
        {
            IRow row = sheet.GetRow(rowIdx);

            if (row != null) return row.Cells[colIdx];

            row = sheet.CreateRow(rowIdx);
            return row.CreateCell(colIdx);
        }

        private ICell GetCell(string cellName)
            => GetCell(GetRowIndex(cellName), GetColumnIndex(cellName));

        /// <summary>
        /// 将下标改为列明
        /// * 从后往前依次除26
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        private static string GetColumnName(int index)
        {
            if (index < 0) throw new Exception("不存在的下标");
            StringBuilder sbdr = new StringBuilder();
            for (; index >= 0; index = (index - index % 26) / 26, --index)
            {
                sbdr.Insert(0, (char)(index % 26 + 'A'));
            }
            return sbdr.ToString();
        }

        /// <summary>
        /// 将列明改为下标
        /// * 从后往前依次乘以26的幂
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        private static int GetColumnIndex(string column)
        {
            if (!Regex.IsMatch(column, "([A-Z]|[a-z])+")) throw new Exception("不存在的列号");
            char[] chars = Regex.Match(column, "([A-Z]|[a-z])+").Value.ToUpper().ToArray();
            return chars.Select((c, i) => (c - 'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1)).Sum() - 1;
        }

        private static int GetRowIndex(string column)
            => Convert.ToInt32(Regex.Match(column, "[0-9]+").Value) - 1;

        private static string GetStringValue(ICell cell)
        {
            if (cell == null)
            {
                return null;
            }

            if (cell.CellType == CellType.Boolean || (cell.CellType == CellType.Formula && cell.CachedFormulaResultType == CellType.Boolean))
            {
                return cell.BooleanCellValue.ToString();
            }

            if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
            {
                return cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
            }

            if (cell.CellType == CellType.Numeric || (cell.CellType == CellType.Formula && cell.CachedFormulaResultType == CellType.Numeric))
            {
                return cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
            }

            return cell.StringCellValue;
        }

        private static string GetNewBackUpFileName(string filePath)
        {
            if (!File.Exists(filePath)) return filePath;

            filePath = GetNewBackUpFileName(Path.GetDirectoryName(filePath) + "\\" + Path.GetFileNameWithoutExtension(filePath) + " - 副本" + Path.GetExtension(filePath));
            return Path.GetFileName(filePath);
        }

        private static string GetNewColumnName(DataColumnCollection dcc, string colName)
        {
            if (!dcc.Contains(colName)) return colName;

            List<DataColumn> colList = dcc.Cast<DataColumn>().ToList();
            const string regSuffix = @"_[0-9]+";
            if (!Regex.IsMatch(colName, regSuffix)) colName += "_";

            string prefix = Regex.Match(colName, @"(.*)_").Value;
            int max = colList.Any(a => Regex.IsMatch(a.ColumnName, prefix))
                        ? colList.Where(a => Regex.IsMatch(a.ColumnName, prefix)).Max(a => Convert.ToInt32(Regex.Match(a.ColumnName, regSuffix).Value.Substring(1))) + 1
                        : 1;

            return prefix + max;
        }

        private static void RenameFile(string oldFile, string newFile)
        {
            if (File.Exists(newFile))
            {
                File.Delete(newFile);
            }
            if (oldFile[0].Equals(newFile[0]))
            {
                File.Move(oldFile, newFile);
                return;
            }

            File.Copy(oldFile, newFile, true);
        }

        private static bool HasData(DataTable dt)
        {
            return dt != null && dt.Rows.Count > 0;
        }
    }
}
